<?php

/** @var $User DpThisUser */
error_reporting(E_ALL);
if(!isset($relPath)) $relPath="./";
require_once $relPath."RoundsInfo.php";
require_once $relPath."DpEnchant.class.php";
require_once $relPath."DpUser.class.php";

define("PROJ_UNAVAILABLE",             "proj_unavail");
define("PROJ_WAITING_FOR_RELEASE",     "proj_waiting_for_release");
define("PROJ_BAD_PROJECT",             "proj_bad_project");
define("PROJ_AVAILABLE",               "proj_avail");
define("PROJ_COMPLETE",                "proj_complete");
define("PROJ_POST_FIRST_AVAILABLE",    "proj_post_first_available");
define("PROJ_POST_FIRST_CHECKED_OUT",  "proj_post_first_checked_out");
define("PROJ_POST_SECOND_AVAILABLE",   "proj_post_second_available");
define("PROJ_POST_SECOND_CHECKED_OUT", "proj_post_second_checked_out");
define("PROJ_CORRECT_AVAILABLE",       "proj_correct_available");
define("PROJ_CORRECT_CHECKED_OUT",     "proj_correct_checked_out");
define("PROJ_SUBMIT_PG_POSTED",        "proj_submit_pgposted");
define("PROJ_DELETE",                  "project_delete");
define("GOOD_WORDS_FILENAME",          "goodwords.txt");
define("BAD_WORDS_FILENAME",           "badwords.txt");
define("SUGGESTED_WORDS_FILENAME",     "suggestedwords.txt");
define("WDIFF_FILENAME",               "wdiff.txt");
define("OCR_FILENAME",                 "ocr.txt");
define("ACTIVE_TEXT_FILENAME",         "activetext.txt");

class DpProject
{
    protected $_projectid;
    protected $_text;
    protected $_row;
    protected $_pagerows;
    protected $_pages;
    protected $_page_objects;
    protected $_error_message = "";
    protected $_lines = array();
    protected $_page_char_offset_array = array();
    protected $_page_byte_offset_array = array();
    protected $_enchanted_words = array();

    public function __construct($projectid = null) {
        $this->_projectid = $projectid;
        $this->query_for_row();
    }

    protected function query_for_row() {
        global $dpdb;
        $sql = "
            SELECT
                p.projectid,
                username,
                p.state,
                SUBSTRING(p.state,1,LOCATE('.',p.state)-1) AS roundid,
	            SUBSTRING(p.state,LOCATE('.',p.state)+1) AS status,
                p.sequence_code,
                p.forum_post_id,
                nameofwork,
                authorsname,
                language,
                other_languages,
                comments,
                difficulty,
                checkedoutby,
                t_last_change_comments, 
                scannercredit,
                postednum,
                clearance,
                titlepage,
                genre,
                postproofer,
                postcomments,
                ppverifier,
                image_source,
                image_preparer,
                text_preparer,
                extra_credits,
                smoothread_deadline,
                SUM(CASE WHEN pp.state LIKE '%".PAGE_AVAIL."'
                   THEN 1 ELSE 0 END) AS available_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_SAVED."'
                    THEN 1 ELSE 0 END) AS completed_count,
                 SUM(CASE WHEN NOT pp.state LIKE '%".PAGE_SAVED."'
                    THEN 1 ELSE 0 END) AS uncompleted_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_OUT."'
                    THEN 1 ELSE 0 END) AS checked_out_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_OUT."'
                    AND UNIX_TIMESTAMP() - COALESCE(
                        round10_time, round9_time,
                        round8_time, round7_time,
                        round6_time, round5_time,
                        round4_time, round3_time, round2_time,
                        round1_time) > 60 * 60 * 4
                    THEN 1 ELSE 0 END) AS out_available_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_TEMP."'
                    THEN 1 ELSE 0 END) AS temp_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_TEMP."'
                    AND UNIX_TIMESTAMP() - COALESCE(
                        round10_time, round9_time,
                        round8_time, round7_time,
                        round6_time, round5_time,
                        round4_time, round3_time, round2_time,
                        round1_time) > 60 * 60 * 4
                    THEN 1 ELSE 0 END) AS temp_available_count,
                 SUM(CASE WHEN pp.state LIKE '%".PAGE_BAD."'
                    THEN 1 ELSE 0 END) AS bad_count,
                 SUM(CASE WHEN round1_time IS NULL
                    THEN 1 ELSE 0 END) AS OCR_count,
                 SUM(CASE WHEN round1_time IS NULL
                    THEN 1 ELSE 0 END) AS OCR_count,
                 MAX(round1_time) AS latest_round1_time,
                 MAX(round2_time) AS latest_round2_time,
                 MAX(round3_time) AS latest_round3_time,
                 MAX(round4_time) AS latest_round4_time,
                 MAX(round5_time) AS latest_round5_time,
                 MAX(round5_time) AS latest_round6_time,
                 MAX(round5_time) AS latest_round7_time,
                 MAX(round5_time) AS latest_round8_time,
                 MAX(round5_time) AS latest_round9_time,
                 MAX(round5_time) AS latest_round10_time,
                 SUM(CASE WHEN pp.fileid IS NULL THEN 0
                    ELSE 1 END) AS page_count,
                 SUM(CASE WHEN LENGTH(pp.image) > 0 THEN 1
                    ELSE 0 END) AS image_count,
                 MAX(COALESCE(round10_time, round9_time,
                               round8_time, round7_time,
                               round6_time, round5_time,
                               round4_time, round3_time, 
                               round2_time, round1_time))
                    AS active_proof_time
            FROM projects AS p
            LEFT JOIN pages AS pp
                ON p.projectid = pp.projectid
            WHERE p.projectid = '{$this->_projectid}'
                AND NOT p.state LIKE '%deleted'
            GROUP BY p.projectid";
        $this->_row = $dpdb->SqlOneRow($sql);
    }


    public function EnchantedWords($langcode) {
        if(! isset($this->_enchanted_words[$langcode])) {
            $this->_enchanted_words[$langcode] =
                new DpEnchantedWords($langcode, 
                        $this->Text(), $this->ProjectId());
        }
        return $this->_enchanted_words[$langcode];
    }

    public function DeleteAll() {
        global $dpdb;
        $projectid = $this->ProjectId();
        $pgs = $dpdb->SqlRows("
            SELECT fileid
            FROM project_pages
            WHERE projectid = '$projectid'");

        foreach($pgs as $pg) {
            $pg = new DpPage($projectid, $pg[0]);
            $pg->Delete();
        }
        $this->LogProjectEvent(PJ_EVT_DELETE);
        $dpdb->SqlExecute("DELETE FROM projects
                           WHERE projectid = '$projectid'");
    }

//    public function ImageThumbUrl($pagename) {
//        /** @var $pg DpPage */
//        $pg = new DpPage($this->ProjectId(), $pagename);
//        return $pg->ThumbUrl(true);
//    }

    private function init_text() {
        global $dpdb;

        $sql = "
            SELECT 
                fileid AS pagename,
                COALESCE(
                NULLIF(round10_text, ''),
                NULLIF(round9_text, ''),
                NULLIF(round8_text, ''),
                NULLIF(round7_text, ''),
                NULLIF(round6_text, ''),
                NULLIF(round5_text, ''),
                NULLIF(round4_text, ''),
                NULLIF(round3_text, ''),
                NULLIF(round2_text, ''),
                NULLIF(round1_text, ''),
                master_text) AS active_text
            FROM pages
            WHERE projectid = '{$this->ProjectId()}'";

        $rows = $dpdb->SqlRows($sql);
        foreach($rows as $row) {
            $name = $row['pagename'];
            $this->_text .= ("\n".$row['active_text']);
            $this->_page_char_offset_array[$name]
                                = mb_strlen($this->_text);
            $this->_page_byte_offset_array[$name]
                                = strlen($this->_text);
        }
    }

    public function ActiveText() {
        return $this->Text();
    }

    public function Text() {
        if(! isset($this->_text)) {
            $this->init_text();
        }
        return $this->_text;
    }

    private function FilterProjectArgs($a_in) {
        assert(is_array($a_in));

        $a_out = array();
        foreach($a_in as $name => $value)
        {
            switch($name)
            {
                case "nameofwork":
                case "authorsname":
                case "username":
                case "language":
                case "other_languages":
                case "genre":
                case "image_source":
                case "checkedoutby":
                case "image_preparer":
                case "text_preparer":
                case "postednum":
                case "clearance":
                case "titlepage":
                case "extra_credits":
                case "difficulty":
                case "comments":
                    $a_out[$name] = $value;
                    continue;

                default:
                    continue;
            }
        }
        return $a_out;
    }

    public function UpdateFromArray($args) {
        $errs = array();
        if(! is_array($args)) {
            $errs[] = _("Update argument is not an array.");
            return $errs;
        }
        $args = $this->FilterProjectArgs($args);

        if(! empty($this->_error_message))
        {
            $errs[] = $this->_error_message;
            return $errs;
        }
        $err = $this->DoUpdateViaParameterizedStatement($args);
        return array($err);
    }


    private function DoUpdateViaParameterizedStatement($setclauses) {
        global $dpdb;
//        global $Context;
/** @var $vals array  */
        $sqlfields = "";
        foreach($setclauses as $name => $value) {
            $sqlfields .= "{$name} = ?,\n";
            $$name = $value;
            $vals[] = &$$name;
        }
            $projectid = $this->ProjectId();
            $sql = "UPDATE projects
                    SET
                        {$sqlfields}
                    modifieddate = UNIX_TIMESTAMP()
                    WHERE projectid = '{$projectid}'";
        $dpdb->SqlExecutePS($sql, $vals);
        return $dpdb->ErrorMessage();
    }

    public function SequenceCode() {
        return $this->_row['sequence_code'];
    }


    private function is_valid_posted_num($postednum) {
        return preg_match('/^[0-9]*$/', $postednum );
    }

    // rules: user is sa
    //        or user may edit anything
    //        or user is project editor

    public function MayBeProofedByActiveUser() {
        global $User;

        if($this->IsMentorRound()) {
        }
        else if($this->IsMenteeRound()) {
        }
        else {
            return $User->IsSiteManager()
                || $User->IsProjectFacilitator()
                || ($User->Username() == $this->PM())
                || $User->IsQualifiedForRound($this->RoundId());
        }
        return false;
    }

    public function UserMayManage() {
        return $this->MayBeManagedByActiveUser();
    }

    public function MayBeManagedByActiveUser() {
        global $User;
        global $dpdb;

        $projectid = $this->ProjectId();
        if( $User->IsSiteManager()
                    || $User->IsProjectFacilitator()
                    || $this->UserIsPM() ) {
            return true;
        }
        $sql = "SELECT 1 FROM projects p, users u
                LEFT JOIN user_roles urmgr
                    ON u.username = urmgr.username
                        AND urmgr.rolename = 'pm'
                LEFT JOIN user_roles ureditor 
                    ON u.username = ureditor.username
                        AND ureditor.rolename = 'editor'
                LEFT JOIN user_project_roles uprpm
                    ON u.username = uprpm.username
                        AND uprpm.projectid = '$projectid'
                        AND uprpm.rolename = 'manager'
                LEFT JOIN user_project_roles upreditor
                    ON u.username = upreditor.username
                        AND upreditor.rolename = 'editor'
                        AND upreditor.projectid = '$projectid'
                WHERE 
                    p.projectid = '$projectid'
                AND
                    u.username = '{$User->Username()}'
                AND
                (
                    urmgr.id IS NOT NULL
                    OR ureditor.id IS NOT NULL
                    OR uprpm.id IS NOT NULL
                    OR upreditor.id IS NOT NULL
                )";
        return $dpdb->SqlExists($sql);
    }

//    public function UserMayProofPage($pagename) {
//        global $dpdb;
//        global $User;
//
//        if($this->UserMayManage()) {
//            return true;
//        }
//        $username = $User->Username();
//        $projectid = $this->ProjectId();
//        $prev_round_index = RoundIndexForId($this->RoundId()) - 1;
//        $usr_clause = ($prev_round_index > 0)
//            ? "AND round{$prev_round_index}_user != '$username'\n"
//            : "";
//
//        $isqual = $dpdb->SqlOneValue($sql);
//    }

    public function UserMayProof() {
        global $User;
        return $User->IsLoggedIn();
    }


    public function UserMaySeeNames() {
        global $User;
//        $username = $User->UserName();
        return 
            $User->IsLoggedIn()
            && (
                $this->MayBeManagedByActiveUser()
                || $this->UserIsPM()
                || $this->UserIsPPVer()
            );
    }


    public function IsMentorRound() {
        return false;
    }

    public function IsMenteeRound() {
        return false;
    }


    public function ActiveUserCompletedPages($maxcount) {
        global $dpdb;
        global $User;
        if(RoundIndexForId($this->RoundId()) < 1) {
            return null;
        }
        $timefield = TimeFieldForRoundId($this->RoundId());
        $userfield = UserFieldForRoundId($this->RoundId());
        $state = "%".PAGE_SAVED;
        $sql = "SELECT 
                    pp.projectid,
                    fileid AS pagename,
                    $timefield AS roundtime,
                    $userfield AS rounduser,
                    pp.state AS page_state,
                    p.state AS project_state
                FROM pages AS pp
                JOIN projects AS p 
                    ON pp.projectid = p.projectid
                WHERE 
                    pp.projectid = '{$this->ProjectId()}'
                    AND $userfield = '{$User->Username()}'
                    AND pp.state LIKE '$state'
                ORDER BY $timefield DESC
                LIMIT {$maxcount}";
        return $dpdb->SqlRows($sql);
    }

    public function ActiveUserUncompletedPages($maxcount) {
        global $dpdb;
        global $User;
        if(RoundIndexForId($this->RoundId()) < 1)
            return null;
        $timefield = TimeFieldForRoundId($this->RoundId());
        $userfield = UserFieldForRoundId($this->RoundId());
        $sql = "SELECT 
                    pp.projectid,
                    fileid AS pagename,
                    $timefield AS roundtime,
                    $userfield AS rounduser,
                    pp.state AS page_state,
                    p.state AS project_state
                FROM pages AS pp
                JOIN projects AS p
                    ON pp.projectid = p.projectid
                WHERE
                    pp.projectid = '{$this->ProjectId()}'
                    AND $userfield = '{$User->Username()}'
                    AND (
                        pp.state LIKE '%out'
                        OR pp.state LIKE '%temp'
                        )
                ORDER BY $timefield DESC
                LIMIT {$maxcount}";
        return $dpdb->SqlRows($sql);
    }

    public function ScannerCredit() {
        return $this->_row['scannercredit'];
    }

    public function AvailableMessageForActiveUser() {
        if(! $this->IsAvailableState())
            return _("Project is unavailable.");
        return $this->IsAvailableForActiveUser()
                ? _("Pages are available.")
                : _("No more pages are available for you.");
    }

    public function IsAvailableForActiveUser() {
        if(! $this->IsAvailableState())
            return false;
        return ($this->NextAvailablePage() != null);
    }

    public function IsAvailableState() {
        return $this->State() == PROJ_AVAILABLE;
    }

    public function IsAvailable() {
        return $this->IsAvailableState()
            && $this->NetAvailableCount() > 0;
    }

    public function IsAvailableForSmoothReading() {
        return ( $this->State() == PROJ_POST_FIRST_CHECKED_OUT
                    && $this->SmoothReadDeadline() > time() );

    }

    public function IsRoundCompleted() {
        return $this->State() == PROJ_COMPLETE
            || $this->UncompletedCount() == 0;
    }

    public function TaskCode() {
        return $this->RoundId();
    }

    public function IsTaskCompleted() {
        return $this->UncompletedCount() == 0;
    }

    public function MaybeSendTaskCompleteEmail() {
        $this->query_for_row();
        if($this->IsTaskCompleted()) {
            $this->SendTaskCompleteEmail();
        }
    }

    private function SendTaskCompleteEmail() {
        global $Context;
        $Context->SendUserEmail($this->PM(),
            "DP-It "
            ._("notification - {$this->TaskCode()} is completed for {$this->Title()}"),
            _("
To the project manager for ".$this->Title().",

This <a href='{$this->ProjectLink()}'>project</a> has completed
Task {$this->TaskCode()}.

Thanks!

The Administration"));
    }

    public function Credits() {
        global $site_name;

        $credit = "";
        foreach(array("PM" => $this->ProjectManager(),
                      "PP" => $this->PPer(),
                      "ImagePreparer" => $this->ImagePreparer(),
                      "TextPreparer" => $this->TextPreparer(),
                      "ExtraCredits" => $this->ExtraCredits(),
                      "ScannerCredit" => $this->ScannerCredit()
                ) as $role => $name) {
            if(empty($name))
                continue;
            $credit .= $name;
//            $usr = new DpUser($name);
//            $safe = $usr->SafeName($role);
//            if(empty($safe))
//                continue;
//            $credit .= (empty($credit)
//                            ? $usr->SafeName()
//                            : ", " . $usr->SafeName());
        }
        return ( empty($credit) ?  "The" : $credit . " and the")
            . " Distributed Proofreading team at {$site_name}.";
    }

    public function LastEditTime() {
        return null;
    }

    public function RoundState() {
        return $this->_row['state'];
    }

    public function Status() {
        return $this->State();
    }

    public function State() {
        return $this->_row['status'];
    }

    public function RoundIndex() {
        return RoundIndexForId($this->RoundId());
    }

    public function RoundId() {
        return $this->_row['roundid'] == ""
            ? "OCR"
            : $this->_row['roundid'];
    }

    public function RoundIdName() {
        return RoundIdName($this->RoundId());
    }

    public function AdvanceRound() {
        global $dpdb;
        if(! $this->UncompletedCount() == 0) {
            assert(false);
            return;
        }

        $projectid = $this->ProjectId();
        $roundid = RoundIdAfter($this->RoundId());

        if(! $roundid) {
            // no more rounds 
            $dpdb->SqlExecute("
                UPDATE projects
                SET state = '".PROJ_COMPLETE."'
                WHERE projectid = '$projectid'");
            $this->SetUnavailable();
            $this->LogProjectEvent(PJ_EVT_COMPLETE);
        }
        else {
            $timefield = TimeFieldForRoundId($roundid);
            $newprojstate = "{$roundid}.".PROJ_AVAILABLE;
            $newpagestate = "{$roundid}.".PAGE_AVAIL;
            $dpdb->SqlExecute("
                UPDATE projects
                SET state = '$newprojstate'
                WHERE projectid = '$projectid'");
            $dpdb->SqlExecute("
                UPDATE pages
                SET state = '$newpagestate',
                $timefield = UNIX_TIMESTAMP()
                WHERE projectid = '$projectid'");
            $this->LogProjectEvent(PJ_EVT_ADVANCE);
        }
    }

    function EventHistory() {
        global $dpdb;
        return $dpdb->SqlRows("
            SELECT 
                projectid,
                event_time,
                who,
                event_type,
                COALESCE(details, '') AS note
            FROM project_events
            WHERE projectid = '{$this->ProjectId()}'");
    }

    function FirstRoundId() {
        return RoundIdForIndex(1);
    }

    function NextRoundDescription() {
        return RoundIdDescription($this->NextRoundId());
    }


    private function image_url($pagename) {
        $pg = $this->Page($pagename);
        return $pg->ImageUrl();
    }

    public function ProjectLink() {
        global $code_url;
        return "$code_url/project.php?projectid={$this->ProjectId()}";
    }

    public function AmazonLink() {
        $s = $this->AuthorsName() ;
        $akey = str_replace(" ", "%20", $s ) ;

        $url = "http://webservices.amazon.com/onca/xml"
        ."?Service=AWSECommerceService"
        ."&AWSAccessKeyId=[Your Access Key ID Here]"
        ."&Operation=ItemSearch"
        ."&SearchIndex=Books"
        ."&Author=".$akey;

        return $url ;
    }

//    public function Url() {
//        return PROJECT_PHP . "projectid=?".$this->ProjectId();
//    }

    public function ProjectUrl() {
        global $projects_url;
        return "$projects_url/{$this->ProjectId()}";
    }

    public function CheckedOutBy() {
        return $this->_row['checkedoutby'];
    }

    public function PPer() {
        return $this->_row['postproofer'];
    }
    
    public function PPVer() {
        return $this->_row['ppverifier'];
    }

    public function UserIsPPVer() {
        global $User;
        return ($this->PPVer() == $User->Username());
    }

    public function UserIsPPer() {
        global $User;
        return ($this->PPer() == $User->Username());
    }

    public function TransientPath() {
        return ProjectTransientPath($this->ProjectId());
    }

    public function UploadPath() {
        return ProjectUploadPath($this->ProjectId());
    }

    public function LoadFilePath() {
        return $this->UploadPath();
    }

    public function ImageFilesInProjectDirectory() {
        $path = $this->UploadPath();
//        $ary = array();
        return sort(glob("$path/*.{png,jpg,gif}", GLOB_BRACE));
    }

    // cached as $this->_pages
    public function PagesArray() {
        global $dpdb;
        if(! isset($this->_pages)) {
            $sql = "
        SELECT
            pp.projectid,
            pp.fileid as pagename,
            pp.image as imagefilename,
            pp.state AS page_state,

            round1_user,
            round2_user,
            round3_user,
            round4_user,
            round5_user,
            round6_user,
            round7_user,
            round8_user,
            round9_user,
            round10_user,
            COALESCE(
               round10_user,
               round9_user, round8_user,
               round7_user, round6_user,
               round5_user, round4_user,
               round3_user, round2_user,
               round1_user, ''
            ) AS last_user,

            round1_time,
            round2_time,
            round3_time,
            round4_time,
            round5_time,
            round6_time,
            round7_time,
            round8_time,
            round9_time,
            round10_time,
            COALESCE(
               round10_time,
               round9_time, round8_time,
               round7_time, round6_time,
               round5_time, round4_time,
               round3_time, round2_time,
               round1_time, ''
            ) AS last_time,

            master_text,
            round1_text,
            round2_text,
            round3_text,
            round4_text,
            round5_text,
            round6_text,
            round7_text,
            round8_text,
            round9_text,
            round10_text,
            COALESCE(
               round10_text,
               round9_text, round8_text,
               round7_text, round6_text,
               NULLIF(round5_text, ''),
               NULLIF(round4_text, ''),
               NULLIF(round3_text, ''),
               NULLIF(round2_text, ''),
               NULLIF(round1_text, ''),
               master_text
            ) AS last_text,

            b_user,
            b_comment,

            p.language,
            p.other_languages,
            p.comments,
            p.username AS projectmanager,
            p.nameofwork AS title,
            p.authorsname AS author,
            p.state AS project_state,
        SUBSTRING(p.state,1,LOCATE(".",p.state)-1) AS projectroundid,
        SUBSTRING(p.state,LOCATE(".",p.state)+1) AS projectstatus
        FROM pages AS pp
        JOIN projects AS p
            ON pp.projectid = p.projectid
        WHERE pp.projectid = '{$this->ProjectId()}'";
            $this->_pages = $dpdb->SqlRows($sql);
        }
        return $this->_pages;
    }

    // queries single table only (pages, or "project" table)
    // cached as $this->_pagerows
    // returns old fieldnames (plus "active_text");
    public function PageRows($is_refresh = false) {
        global $dpdb;
        if(! isset($this->_pagerows) || $is_refresh) {
            $this->_pagerows = $dpdb->SqlRows("
                SELECT 
                    projectid,
                    fileid,
                    image,
                    round1_user,
                    round2_user,
                    round3_user,
                    round4_user,
                    round5_user,
                    round6_user,
                    round7_user,
                    round8_user,
                    round9_user,
                    round10_user,

                    round1_time,
                    round2_time,
                    round3_time,
                    round4_time,
                    round5_time,
                    round6_time,
                    round7_time,
                    round8_time,
                    round9_time,
                    round10_time,

                    round1_text,
                    round2_text,
                    round3_text,
                    round4_text,
                    round5_text,
                    round6_text,
                    round7_text,
                    round8_text,
                    round9_text,
                    round10_text,
                    COALESCE(
                        NULLIF(round10_text, ''),
                        NULLIF(round9_text, ''),
                        NULLIF(round8_text, ''),
                        NULLIF(round7_text, ''),
                        NULLIF(round6_text, ''),
                        NULLIF(round5_text, ''),
                        NULLIF(round4_text, ''),
                        NULLIF(round3_text, ''),
                        NULLIF(round2_text, ''),
                        NULLIF(round1_text, ''),
                        master_text
                    ) AS active_text
                FROM pages
                WHERE projectid = '{$this->ProjectId()}'
                ORDER BY fileid");
        }
        return $this->_pagerows;
    }

    public function ActivePageRows() {
        global $dpdb;
        static $_rows;
        if(! isset($_rows)) {
            $_rows = $dpdb->SqlRows("
                SELECT 
                    projectid,
                    fileid AS pagename,
                    image,
                    COALESCE(
                       round10_user,
                       round9_user, round8_user,
                       round7_user, round6_user,
                       round5_user, round4_user,
                       round3_user, round2_user,
                       round1_user, ''
                    ) AS username,

                    COALESCE(
                       round10_time,
                       round9_time, round8_time,
                       round7_time, round6_time,
                       round5_time, round4_time,
                       round3_time, round2_time,
                       round1_time, ''
                    ) AS round_time,

                    COALESCE(
                        NULLIF(round10_text, ''),
                        NULLIF(round9_text, ''),
                        NULLIF(round8_text, ''),
                        NULLIF(round7_text, ''),
                        NULLIF(round6_text, ''),
                        NULLIF(round5_text, ''),
                        NULLIF(round4_text, ''),
                        NULLIF(round3_text, ''),
                        NULLIF(round2_text, ''),
                        NULLIF(round1_text, ''),
                        master_text
                    ) AS active_text
                FROM pages
                WHERE projectid = '{$this->ProjectId()}'
                ORDER BY fileid");
        }
        return $_rows;
    }

    public function OCRText() {
        $t = "";
        $rows = $this->PageOCRRows();
        foreach($rows as $row) {
            $t .= ($row['text']."\n");   
        }
        return $t;
    }

    private function PageOCRRows() {
        global $dpdb;
        return $dpdb->SqlRows("
                SELECT 
                    fileid AS pagename,
                    image AS imagefile,
                    NULL as proofers,
                    master_text AS text
                FROM pages
                WHERE projectid = '{$this->ProjectId()}'
                ORDER BY fileid");
    }

    public function PageRoundTextArray($roundid) {
        global $dpdb;
        $index = RoundIndexForId($roundid);
        return $dpdb->SqlRows("
                SELECT 
                    fileid AS pagename,
                    image AS imagefile,
                    round{$index}_text AS text,
                CONCAT(
                    CASE WHEN round1_user IS NULL OR round1_user = ''
                        THEN '' ELSE CONCAT('\\\\', round1_user) END,
                    CASE WHEN round2_user IS NULL OR round2_user = ''
                        THEN '' ELSE CONCAT('\\\\', round2_user) END,
                    CASE WHEN round3_user IS NULL OR round3_user = ''
                        THEN '' ELSE CONCAT('\\\\', round3_user) END,
                    CASE WHEN round4_user IS NULL OR round4_user = ''
                        THEN '' ELSE CONCAT('\\\\', round4_user) END,
                    CASE WHEN round5_user IS NULL OR round5_user = ''
                        THEN '' ELSE CONCAT('\\\\', round5_user) END,
                    CASE WHEN round6_user IS NULL OR round6_user = ''
                        THEN '' ELSE CONCAT('\\\\', round6_user) END,
                    CASE WHEN round7_user IS NULL OR round7_user = ''
                        THEN '' ELSE CONCAT('\\\\', round7_user) END,
                    CASE WHEN round8_user IS NULL OR round8_user = ''
                        THEN '' ELSE CONCAT('\\\\', round8_user) END,
                    CASE WHEN round9_user IS NULL OR round9_user = ''
                        THEN '' ELSE CONCAT('\\\\', round9_user) END,
                    CASE WHEN round10_user IS NULL OR round10_user = ''
                        THEN '' ELSE CONCAT('\\\\', round10_user) END,
                    '\\\\'
                ) AS proofers
                FROM pages
                WHERE projectid = '{$this->ProjectId()}'
                ORDER BY fileid");
    }

    // in the following, $roundid is the last text
    // to include if not null
    public function PageActiveTextArray($roundid) {
        global $dpdb;
        $ary = array();
        if($this->RoundId() == "OCR") {
            $ary[] = "master_text";
        }
        else {
            $rdx = RoundIndexForId($roundid);
            for($i = $rdx; $i >= 0; $i--) {
                $ary[] = "NULLIF(".TextFieldForRoundIndex($i).", '')";   
            }
        }

        $sql = "
                SELECT fileid AS pagename,
                COALESCE(
                ".implode(", ", $ary)."
                ) AS text,
                CONCAT(
                    CASE WHEN round1_user IS NULL OR round1_user = ''
                        THEN '' ELSE CONCAT('\\\\', round1_user) END,
                    CASE WHEN round2_user IS NULL OR round2_user = ''
                        THEN '' ELSE CONCAT('\\\\', round2_user) END,
                    CASE WHEN round3_user IS NULL OR round3_user = ''
                        THEN '' ELSE CONCAT('\\\\', round3_user) END,
                    CASE WHEN round4_user IS NULL OR round4_user = ''
                        THEN '' ELSE CONCAT('\\\\', round4_user) END,
                    CASE WHEN round5_user IS NULL OR round5_user = ''
                        THEN '' ELSE CONCAT('\\\\', round5_user) END,
                    CASE WHEN round6_user IS NULL OR round6_user = ''
                        THEN '' ELSE CONCAT('\\\\', round6_user) END,
                    CASE WHEN round7_user IS NULL OR round7_user = ''
                        THEN '' ELSE CONCAT('\\\\', round7_user) END,
                    CASE WHEN round8_user IS NULL OR round8_user = ''
                        THEN '' ELSE CONCAT('\\\\', round8_user) END,
                    CASE WHEN round9_user IS NULL OR round9_user = ''
                        THEN '' ELSE CONCAT('\\\\', round9_user) END,
                    CASE WHEN round10_user IS NULL OR round10_user = ''
                        THEN '' ELSE CONCAT('\\\\', round10_user) END,
                    '\\\\'
                ) AS proofers
                FROM pages
                WHERE projectid = '{$this->ProjectId()}'
                ORDER BY fileid";
        return $dpdb->SqlRows($sql);
    }

    public function Page($pagename) {
        if(!empty($pagename))
            return new DpPage($this->ProjectId(), $pagename);
        else {
            assert(false);
            return null;
        }
    }

    public function ProjectPages() {
        static $pgs ;

        if(! isset($pgs ) ) {
            $projectid = $this->ProjectId();
            $pgs = array();
            foreach($this->PageRows() as $row) {
                $pagename = $row['fileid'];
                $pgs[$pagename] = new DpPage($projectid, $pagename);
            }
        }
        return $pgs;
    }

    public function Exists() {
        return !empty($this->_row['projectid']);
    }

/*
    public function RequestPageTask($projectid, $pagecode, $taskcode) {
        global $dpdb;

        $sql = "
            SELECT
                pp.*
            FROM
                pages AS pp
            LEFT JOIN
                pagetasks AS pt
            ON
                pp.projectid = pt.projectid
                AND pp.pagecode = pt.pagecode
            WHERE
                pp.projectid = '$projectid'
                AND pp.pagecode = '$pagecode'
                AND pt.taskcode = '$taskcode'
                AND pt.sequence = $seq";

        return $dpdb->SqlOneRow($sql);
    }
    */

/*
    public function PageCountsByState() {
        global $dpdb;
        return $dpdb->SqlRows("
                SELECT 
                    SUBSTRING(state, 9) AS page_state,
                    COUNT(*) AS state_count
                FROM pages
                WHERE 
                    projectid = '{$this->ProjectId()}'
                GROUP BY SUBSTRING(state, 9)");
    }
*/

    public function NetAvailableCount() {
        return $this->AvailableCount() + $this->RetrievableCount();
    }

    public function NetAvailablePercent() {
        return $this->PageCount() > 0
            ? 100 * $this->NetAvailableCount() / $this->PageCount()
            : 0;
    }

    public function AvailablePercent() {
        return $this->PageCount() > 0
            ? 100 * $this->AvailableCount() / $this->PageCount()
            : 0;
    }

    public function InProgressPercent() {
        return $this->PageCount() > 0
            ? 100 * $this->CheckedOutCount() / $this->TempCount()
            : 0;
    }

    public function ReservedPercent() {
        return $this->PageCount() > 0
            ? 100 * $this->ReservedCount() / $this->PageCount()
            : 0;
    }

    public function RetrievablePercent() {
        return $this->PageCount() > 0
            ? 100 * $this->RetrievableCount() / $this->PageCount()
            : 0;
    }

    public function CompletedPercent() {
        return $this->PageCount() > 0
            ? 100 * $this->CompletedCount() / $this->PageCount()
            : 0;
    }

    public function TempAvailableCount() {
        return $this->_row['temp_available_count'];
    }

    private function CheckedOutAvailableCount() {
        return $this->_row['out_available_count'];
    }

    public function AvailableCount() {
        return $this->_row['available_count'];
    }

    public function InProgressCount() {
        return $this->CheckedOutCount() + $this->TempCount();
    }

    public function ReservedCount() {
        return $this->InProgressCount() - $this->RetrievableCount();
    }

    public function RetrievableCount() {
        return $this->CheckedOutAvailableCount()
                             + $this->TempAvailableCount();
    }

    public function CompletedCount() {
        return $this->_row['completed_count'];
    }

    public function UncompletedCount() {
        return $this->_row['uncompleted_count'];
    }

    public function CheckedOutCount() {
        return $this->_row['checked_out_count'];
    }

    public function CheckedOutPercent() {
        return $this->PageCount() > 0
            ? 100 * $this->CheckedOutCount() / $this->PageCount()
            : 0;
    }

    public function TempCount() {
        return $this->_row['temp_count'];
    }

    public function TempPercent() {
        return $this->PageCount() > 0
            ? 100 * $this->TempCount() / $this->PageCount()
            : 0;
    }

    public function BadCount() {
        return $this->_row['bad_count'];
    }

/*
    public function SetTasks( $atasks ) {
        global $dpdb;

        $tlist = "" ;
        foreach( $atasks as $t ) {
            $tlist .= ",\n'$t'" ;
        }
        $tlist = substr( $tlist, 1 ) ;

        $sql = "DELETE FROM projecttasks
                WHERE projectid = '{$this->_row['projectid']}'";
        $i = $dpdb->SqlExecute($sql) ;
            
        if( count( $atasks ) > 0 ) {
            $sql = "INSERT INTO projecttasks
                    (
                        projectid,
                        taskcode, 
                        sequencenumber
                    )
                    SELECT
                        '{$this->_row['projectid']}',
                        t.taskcode,
                        t.sequencenumber
                    FROM
                        projects AS p,
                        tasks AS t
                    WHERE
                        t.taskcode IN
                        ( $tlist )
                        AND p.projectid = '{$this->_row['projectid']}'";
            $i = $dpdb->SqlExecute( $sql ) ;
            assert( $i > 0 ) ;
        }
    }

    public function ProjectTasks() {
        global $dpdb;
        static $_tasks ;
        if(!isset($_tasks))
        {
            $_tasks = array() ;
            $sql = "SELECT DISTINCT pt.taskcode
                    FROM projecttasks AS pt
                    JOIN tasks AS t ON pt.taskcode = t.taskcode
                    WHERE projectid = '{$this->_row['projectid']}'
                    AND t.isscheduled != 0";
            foreach( $dpdb->SqlRows($sql) as $row ) {
                $_tasks[$row[0]] = $row[0];
            }
        }
        return $_tasks ;
    }
*/

    public function CloneProject() {
        global $Context;
        global $dpdb;

        $new_project_id = $Context->NewProjectId();
        $sql = "
            INSERT INTO projects
            (
                 projectid,
                 username,
                 state,
                 nameofwork,

                 authorsname,
                 language,
                 other_languages,
                 comments,
                 difficulty,

                 scannercredit,
                 clearance,
                 titlepage,

                 genre,
                 image_source,
                 image_preparer,
                 text_preparer,
                 extra_credits
             )
             SELECT

                '{$new_project_id}',
                p.username,
                'OCR.".PROJ_WAITING_FOR_RELEASE ."',
                CONCAT('Clone of ', p.nameofwork),
                           
                p.authorsname,
                p.language,
                p.other_languages,
                p.comments,
                p.difficulty,
                           
                p.scannercredit,
                p.clearance,
                p.titlepage,
                           
                p.genre,
                p.image_source,
                p.image_preparer,
                p.text_preparer,
                p.extra_credits
                           
            FROM projects AS p
            WHERE projectid = '{$this->ProjectId()}'";
//        echo $sql;
//        dump($sql);
//        die();
        $dpdb->SqlExecute($sql);
        $this->LogProjectEvent(PJ_EVT_CLONE, 
                                "to {$new_project_id}");
        return $new_project_id;
    }

/*
    public function AddOrReplacePage(
                    $pagename, $imagepath, $text) {
        global $dpdb;

        $projectid  = $this->ProjectId();
        $pagestate  = $this->RoundId().".page_avail";
        $timefield  = ($this->RoundId() == "OCR"
                        ? ""
                        : TimeFieldForRoundId($this->RoundId())
                    );

        $textfield  = ($this->RoundId() == "OCR"
                        ? "master_text"
                        : TextFieldForRoundId($this->RoundId())
                    );

        $isupdate   = $dpdb->SqlExists("
                    SELECT 1 FROM project_pages
                    WHERE projectid = '{$projectid}'
                        AND fileid = '{$pagename}'");

        if($imagepath != "" && file_exists($imagepath)) {
            $imagebase = basename($imagepath);
            if($imagebase != "") {
                $toimagepath 
                    = build_path($this->ProjectPath(), $imagebase);
                if(file_exists($toimagepath)) {
                    unlink($toimagepath);
                }
                // dump("copy $imagepath to $toimagepath");
                copy($imagepath, $toimagepath);
                unlink($imagepath);
            }
        }
        else {
            $imagebase = "";
        }

        if(! is_utf8($text)) {
            $text = to_utf8($text);
        }

        if(! $isupdate) {
//            $pg = new DpProtoPage($projectid, $pagename, $this);
             $sql = "INSERT INTO project_pages
                     SET projectid = ?,
                         fileid = ?,
                         state = ?";
            $args = array(&$projectid, &$pagename, &$pagestate);

            if($imagebase != "") {
                $sql .= ",
                        image = ?";
                $args[] = &$imagebase;
            }

            if($text != "") {
                $sql .= ",
                        $textfield = ?";
                $args[] = &$text;
            }

            if($timefield != "") {
                $sql .= ",
                        $timefield = UNIX_TIMESTAMP()";
            }

//            dump($args);
//            dump($sql);
//            die();
            $ret = $dpdb->SqlExecutePS($sql, $args);
            assert($ret == 1);
            if($ret != 1) {
                dump($sql);
                dump($args);
                die();
            }
        }
        else {
            if($imagebase != "") {
                $sql = "UPDATE project_pages
                        SET {$textfield} = ?, image = ?
                        WHERE projectid = ?
                            AND fileid = ?";
                $args = array(
                    &$text, &$imagebase, &$projectid, &$pagename);
            }
            else {
                $sql = "UPDATE project_pages
                        SET {$textfield} = ?
                        WHERE projectid = ?
                            AND fileid = ?";
                $args = array(&$text, &$projectid, &$pagename);
            }
            $dpdb->SqlExecutePS($sql, $args);
        }
        // $this->LogPageEvent( $pagename, $action);
    }
*/

    public function AvailableTasks() {
        global $dpdb;
        global $User ;
        static $_tasks ;

        if( empty( $_tasks ) ) {
            $username = $User->UserName() ;
            $_tasks = array() ;
            $sql = "SELECT DISTINCT pt.taskcode
                    FROM pagetasks AS pt
                    JOIN tasks AS t ON pt.taskcode = t.taskcode
                    JOIN userquals AS uq ON t.qualcode = uq.qualcode
                        AND uq.username = '$username'

                    LEFT JOIN
                        pagetasks AS pt0
                    ON
                        pt.projectid = pt0.projectid
                        AND pt.pagecode = pt0.pagecode
                        AND pt.sequencenumber > pt0.sequencenumber
                        AND pt0.checkouttime IS NULL

                    LEFT JOIN
                        pagetasks AS ptu
                    ON
                        pt.projectid = ptu.projectid
                        AND pt.pagecode = ptu.pagecode
                        AND ptu.username = '$username'

                    WHERE 
                        pt.projectid = '{$this->ProjectId()}'
                        AND pt.checkouttime IS NULL
                        AND pt0.pagetaskid IS NULL
                        AND ptu.pagetaskid IS NULL

                    ORDER BY
                        pt.pagecode";
            $_tasks = $dpdb->SqlObjects($sql) ;
        }
        return $_tasks;
    }

//    public function IsProjectTask($taskcode) {
//        $t = $this->ProjectTasks();
//        $j = isset($t[$taskcode])?"yes":"no";
//        return isset( $t[$taskcode] ) ;
//    }

    private function _proj_unzip_dir() {
        return $this->ProjectPath() . '/ziptemp';
    }

//    public function Unzip($zippath) {
//        exec("unzip -o -j $zippath -d ".$this->proj_unzip_dir());
//    }

    public function PageCount() {
        return $this->_row['page_count'];
    }

    public function ImageCount() {
        return $this->_row['image_count'];
    }

/*
    public function GetOnePage($pagecode) {
        assert(! empty( $pagecode ) ) ;
        $pgs = $this->ProjectPages() ;
        if(! empty($pgs ) ) {
            return $this->ProjectPages()->Page($pagecode);
        }
        else {
            require_once "PageClass.inc";
            return new ProjectPage($this->ProjectId(), $pagecode) ;
        }
    }
*/

    public function ProjectId() {
        return $this->_projectid;
    }

    public function ProjectPath() {
        return ProjectPath($this->ProjectId());
    }

    public function LastPostTime() {
        return $this->_row['post_time'];
    }

    public function PostComments() {
        return html_nowrap($this->_row['postcomments']);
    }

    public function Comments() {
        return $this->_row['comments'];
    }

    public function LastCommentChangeTime() {
        return $this->_row['t_last_change_comments'];
    }

    public function PM() {
        return $this->ProjectManager();
    }

    public function SetPM($pm) {
        $this->_row['username'] = $pm;
        $this->LogProjectEvent(PJ_EVT_SET_PM, "to $pm");
    }

    public function ProjectManager() {
        return $this->_row['username'];
    }

//    public function LastTaskCompletionTime() {
//        require_once "DpDateClass.inc";
//        $d = new DpDate($this->_row['lasttaskcompletiontime']);
//        return $d->PrettyDate();
//    }

    public function ImageSource() {
        return $this->_row['image_source'];
    }

    public function SetImageSource($src) {
        $this->_row['image_source'] = $src;
    }

    public function SmoothreadDeadline() {
        return $this->_row['smoothread_deadline'];
    }

    // All the project thumbnails are in a directory
    public function ThumbPath() {
        return $this->ProjectPath()."/thumbs"; // name/location of generated thumbnail.
    }

    public function TitleAuthor() {
        return "
            <div>
                <h1>{$this->Title()}</h1>
                <h2>{$this->Author()}</h2>
            </div>\n";
    }
    public function Title() {
        return $this->_row['nameofwork'];
    } 

    public function NameOfWork() {
        return $this->_row['nameofwork'];
    }

    public function SetNameOfWork($name) {
        $this->_row['nameofwork'] = $name;

    }

    public function Author() {
        return $this->_row['authorsname'];
    }

    public function AuthorsName() {
        return $this->_row['authorsname'];
    }

    public function SetAuthorsName($author) {
        $this->_row['authorsname'] = $author;
    }

    public function LanguageCode() {
        return $this->_row['language'];
    }

    public function LanguageName() {
        global $Context;
        return $Context->LanguageName($this->LanguageCode());
    }

    public function OtherLanguageCodes() {
        return $this->_row['other_languages'];
    }

    public function LanguageCodesArray() {
        $codes = preg_split("/\s+/", $this->OtherLanguageCodes());
        array_push($codes, $this->LanguageCode());
        if(isset($codes['']))
            unset($codes['']);
        return array_unique($codes);
    }

    public function Clearance() {
        return empty( $this->_row['clearance'] )
            ? "none"
            :  $this->_row['clearance'] ;
    }

    public function TitlePage() {
        return  $this->_row['titlepage'] ;
    }

    public function TitlePageUrl() {
        return raw_url_for_image_server(
                    $this->ProjectId(), $this->TitlePage());
    }

    public function SetClearance($clearance) {
        $this->_row['clearance'] = $clearance;
    }

    public function SetTitlePage($titlepage) {
        $this->_row['titlepage'] = $titlepage;
    }

//    public function NumberPageTasksAvailable() {
//        return 0;
//    }
//
//    public function NumberPageTasksCheckedOut() {
//        return 0;
//    }
//
//    private function PageTaskTimes() {
//        static $_rows ;
//        if(!isset($_rows)) {
//            $_rows = $this->PageTasks() ;
//        }
//        return $_rows;
//    }
//
//    public function PageTaskTime($pagecode, $taskcode) {
//        $times = $this->PageTaskTimes();
//        return $times[$pagecode][$taskcode];
//    }

/*
    public function ProjectPageTasks() {
        static $iPgTasks;
        if(!isset($iPgTasks)) {
            require_once "PageTaskClass.inc";
            $iPgTasks = new PageTasks($this->ProjectId()) ;
        }
        return $iPgTasks ;
    }
*/

    public function PostedNumber() {
        return $this->_row['postednum'];
    }

    public function SetPostedNumber($num) {
        $this->_row['postednum'] = $num;
    }

    public function Difficulty() {
        return empty( $this->_row['difficulty'] )
            ? ""
            : $this->_row['difficulty'] ;
    }

    public function CreateForumThread() {
        global $Context;
        $subj = "{$this->Title()} by {$this->Author()}";
        $projlink = link_to_project($this->ProjectId(), 
                                        $this->Title());
        $msg = "Forum thread for discussing {$projlink} 
                by {$this->Author()}. 

                The project manager is {$this->PM()}.";

        $tid = $Context->CreateProjectThread($subj, $msg);
        assert($tid > 0);
        $this->SetForumTopicId($tid);
        return $tid;
    }

    public function ForumTopicId() {
        return $this->_row['forum_post_id'];
    }

    public function SetForumTopicId($id) {
        global $dpdb;

        $dpdb->SqlExecute("
            UPDATE projects 
            SET forum_post_id = $id
            WHERE 
                projectid = '{$this->ProjectId()}'");
        $this->_row['forum_post_id'] = $id;
    }

    public function ForumTopicUrl() {
        $id = $this->ForumTopicId();
        if($id <= 0) {
            $id = $this->CreateForumThread();
            assert($id > 0);
        }
        return url_for_project_thread($id);
    }

    public function LastForumPostDate() {
        global $Context;
        return $this->ForumTopicId() 
            ? null
            : $Context->LatestTopicPostTime($this->ForumTopicId());
    }

    public function ForumTopicIsEmpty() {
        global $Context;
        return $this->ForumTopicId() > 0
            && $Context->ForumTopicReplyCount(
                        $this->ForumTopicId()) > 0;
    }

    public function Genre() {
        return $this->_row['genre'];
    }

    public function TextPreparer() {
        return $this->_row['text_preparer'];
    }

    public function ImagePreparer() {
        return $this->_row['image_preparer'];
    }

    public function ExtraCredits() {
        return $this->_row['extra_credits'];
    }

    public function SetDifficulty($val) {
        $this->_row['difficulty'] = $val;
    }

    public function SetGenre($val) {
        $this->_row['genre'] = $val;
    }

    public function SetTextPreparer($val) {
        $this->_row['text_preparer'] = $val;
    }

    public function SetImagePreparer($val) {
        $this->_row['image_preparer'] = $val;
    }

    public function SetExtraCredits($val) {
        $this->_row['extra_credits'] = $val;
    }

    public function UserCheckedOutPageCount() {
        global $User;
        global $dpdb;
        $sql = "SELECT COUNT(1)
                FROM pages
                WHERE projectid = '{$this->Projectid()}'
                    AND (
                        state LIKE '%out'
                        OR state LIKE '%temp'
                    )
                    AND COALESCE(round10_user, round9_user,
                        round8_user, round7_user, round6_user,
                        round5_user, round4_user,
                        round3_user, round2_user, round1_user)
                        = '{$User->Username()}'";
        return $dpdb->SqlOneValue($sql);
    }

    public function UserSavedPageCount() {
        return $this->_row['my_saved_count'] ;
    }

//    public function UserPageCounts2() {
//        return $this->UserActivePageCount()
//            . " / " . $this->UserSavedPageCount() ;
//    }

    public function UserIsPM() {
        global $User;
        return $this->PM() == $User->UserName() ;
    }

    public function UserMayPostProof() {
        return $this->UserIsPM();
    }

    public function UserMayMentee() {
        return true;
    }

    public function UserMayMentor() {
        return true;
    }

    // public function DeleteAllPages() {
        // $this->PagesArray()->DeleteAll();
        // $this->LogProjectEvent(PJ_EVT_DELETE_PAGES);
    // }

//    public function AddFilesFrom($sets) {
//        $this->ProjectPages()->AddFromFilesets($sets);
//    }

    private function set_property($name, $value) {
        $this->_row[$name] = $value;
    }

    public function AvailableForCheckoutCount() {
        $ret = 0;
        $timefield = TimeFieldForRoundId($this->RoundId());

        foreach($this->PagesArray() as $row) {
            if(preg_match("/page_avail/", $row['state']) > 0
            && minutes_ago(intval($row[$timefield])) > 60 * 4) {
                $ret++;
            }
        }
        return $ret;
    }

    public function NextAvailablePage() {
        if(! $this->IsAvailableState()) {
            return null;
        }

        $page = $this->next_available_page_for_user();
        if(! $page)
            $page = $this->next_retrievable_page_for_user();
        return $page;
    }

    public function CheckOutNextAvailablePage() {
        $page = $this->NextAvailablePage();
        if($page != null) {
            $page->CheckOutPage();
            return $page;
        }

        $page = $this->first_user_uncompleted_page();

        if($page != null) {
            $page->ResumePage();
            return $page;
        }

        $page = $this->next_retrievable_page_for_user();
        if($page != null) {
            $page->CheckOutPage();
        }
        return null;
    }

    private function next_available_page_for_user() {
        global $dpdb;
        global $User;

        if(! $this->IsAvailableState())
            return null;

        $roundid = $this->RoundId();
        $projectid = $this->ProjectId();
        $username = $User->Username();
        $pagestate = sprintf("%s.%s", $roundid, PAGE_AVAIL);

        $sql = "
            SELECT MIN(fileid)
            FROM pages
            WHERE projectid = '$projectid'
            AND state = '$pagestate'";

        if($this->RoundIndex() > 1)
        {
            $prevroundid = $this->PreviousRoundId();
            $prevuserfield = UserFieldForRoundId($prevroundid);
            $sql .= "
                AND {$prevuserfield} != '$username'";
        }
        $pagename = $dpdb->SqlOneValue($sql);

        return (empty($pagename))
            ? null
            : $this->Page($pagename);
    }

    private function first_user_uncompleted_page() {
        global $dpdb;
//        global $page_lease_seconds;
        global $User;

        $projectid = $this->ProjectId();
//        $roundid   = $this->RoundId();
//        $timefield = TimeFieldForRoundId($this->RoundId());
//        $userfield = UserFieldForRoundId($this->RoundId());
//        $state     = sprintf("%s.%s", $roundid, PAGE_OUT);
        $username  = $User->Username();

        $sql = "SELECT fileid AS pagename
                FROM pages
                WHERE projectid = '$projectid'
                    AND (
                        state LIKE '%out'
                        OR state LIKe '%temp'
                    )
                    AND COALESCE(round10_user, round9_user,
                        round8_user, round7_user, round6_user,
                        round5_user, round4_user,
                        round3_user, round2_user, round1_user)
                        = '$username'
                ORDER BY COALESCE(round10_time, round9_time,
                    round8_time, round7_time, round6_time,
                    round5_time, round4_time,
                        round3_time, round2_time, round1_time) DESC
                LIMIT 1";
        $pagename = $dpdb->SqlOneValue($sql);
        return ($pagename != "")
            ? $this->Page($pagename)
            : null;
    }

    function next_retrievable_page_for_user() {
        global $dpdb, $User;
        $username = $User->Username();
        $projectid = $this->ProjectId();
    // grab someone else's page if necessary and available.
        $sql = "
            SELECT fileid AS pagename
            FROM pages
            WHERE projectid = '$projectid'
                AND COALESCE(round10_time, round9_time,
                    round8_time, round7_time, round6_time,
                    round5_time, round4_time,
                    round3_time, round2_time, round1_time)
                    < ( (UNIX_TIMESTAMP() - 60 * 60 * 4) )
                AND
                (
                    state LIKE '%out'
                    OR state LIKE '%temp'
                )
                AND COALESCE(round10_user, round9_user,
                    round8_user, round7_user, round6_user,
                    round5_user, round4_user,
                    round3_user, round2_user, round1_user)
                    != '$username'
            ORDER BY COALESCE(round10_time, round9_time,
                round8_time, round7_time, round6_time,
                round5_time, round4_time,
                    round3_time, round2_time, round1_time) DESC
            LIMIT 1";
        $pagename = $dpdb->SqlOneValue($sql);

        return ($pagename != "") 
            ? $this->Page($pagename)
            : null;
    }

    public function MostRecentUserProofDate() {
        global $dpdb;
        global $User;
        $username = $User->UserName();
//        $i = RoundIndexForId($this->RoundId());
        return $dpdb->SqlOneValue ("
                SELECT MAX(event_time) 
                FROM page_events
                WHERE username = '$username'
                    AND projectid = '{$this->ProjectId()}'");
    }

    public function MostRecentSavePageDate() {
        global $dpdb;
        return $dpdb->SqlOneValue ("
                SELECT MAX(event_time) 
                FROM page_events
                WHERE projectid = '{$this->ProjectId()}'
                AND event_type = 'save_done'");
    }

    private function ActiveRoundProofTime($roundid) {
        $idx = RoundIndexForId($roundid);
        if(empty($idx))
            return null;
        $fld = "latest_round{$idx}_proof_time";
        return $this->_row[$fld];
    }

    private function ActiveRoundDescription() {
        return RoundIdDescription($this->RoundId());
    }

    private function PreviousRoundId() {
        return PreviousRoundIdForRoundId($this->Roundid());
    }

    private function NextRoundId() {
        return NextRoundIdForRoundId($this->Roundid());
    }

    public function ToggleAvailable() {
        if($this->IsAvailableState()) {
            $this->SetUnavailable();
        }
        else {
            $this->SetAvailable();
        }
    }

    public function SetAvailable() {
        if(! $this->Exists())
            return;
        if($this->ForumTopicId() == 0) {
            $this->CreateForumThread();
        }
        $newstate = $this->RoundId().".".PROJ_AVAILABLE;
        $this->SetState($newstate);
        $this->LogProjectEvent(PJ_EVT_ACTIVATE);
    }

    public function SetUnavailable() {
        $newstate = $this->RoundId().".".PROJ_UNAVAILABLE;
        $this->SetState($newstate);
        $this->LogProjectEvent(PJ_EVT_DEACTIVATE);
    }

    public function SetState($newstate) {
        global $dpdb;
        $dpdb->SqlExecute("
            UPDATE projects 
            SET state = '$newstate' 
            WHERE projectid = '{$this->ProjectId()}'");
        $this->_row['state'] = $newstate;
        list($this->_row['roundid'], $this->_row['status'])
            = preg_split("/\./", $newstate);
    }

    public function DeletePage($pagename) {
        // global $dpdb;

        $pg = new DpPage($this->ProjectId(), $pagename);
        $pg->Delete();
        // $sql = "
            // UPDATE project_pages
            // SET state = 'deleted',
            // is_active = 0
            // WHERE
                // projectid = {$this->ProjectId()}
                // AND fileid = '$pagename'";
        // $dpdb->SqlExecute($sql);
    }

    public function DeletePages($pagenames) {
        // global $dpdb;
        foreach($pagenames as $pgname) {
            $pg = new DpPage($this->ProjectId(), $pgname);
            $pg->Delete();
        }

        // $pagestr = "'".implode("', '", $pagenames)."'";
        // $sql = "UPDATE project_pages
                // SET state = 'deleted',
                    // is_active = 0
                // WHERE
                    // projectid = {$this->ProjectId()}
                // AND fileid IN (".$pagestr.")";
        // $dpdb->SqlExecute($sql);
    }

    public function ClearPages($pagenames) {
        foreach($pagenames as $pgname) {
            /** @var $pg DpPage */
            $pg = new DpPage($this->ProjectId(), $pgname);
            $pg->Clear($this->RoundId());
        }
    }


    public function ClearPage($pagename) {
        global $dpdb;

        $userfield = UserFieldForRoundId($this->RoundId());
        $timefield = TimeFieldForRoundId($this->RoundId());
        $textfield = TextFieldForRoundId($this->RoundId());
        $dpdb->SqlExecute("
            UPDATE pages
            SET state = '{$this->RoundId()}.".PAGE_AVAIL."',
                $userfield = NULL,
                $timefield = NULL,
                $textfield = NULL
            WHERE
                projectid = '{$this->ProjectId()}'
                AND fileid = '$pagename'");
    }


    public function PageNameBefore($pagename) {
        global $dpdb;
        $sql = "
            SELECT MAX(fileid) FROM pages
            WHERE projectid = '{$this->ProjectId()}'
                AND fileid < '$pagename'";
        return $dpdb->SqlOneValue($sql);
    }

    public function PageNameAfter($pagename) {
        global $dpdb;
        $sql = "
            SELECT MIN(fileid) FROM pages
            WHERE projectid = '{$this->ProjectId()}'
                AND fileid > '$pagename'";
        return $dpdb->SqlOneValue($sql);
    }

    // find same round, same proofer, prev page.
    public function ProoferRoundPageNameBefore($pagename, $roundid) {
        global $dpdb;
        $userfield = UserFieldForRoundId($roundid);
        $sql = "
            SELECT MAX(pp2.fileid) 
            FROM pages AS pp
            JOIN pages AS pp2 
                ON pp.projectid = pp2.projectid
                AND pp.{$userfield} = pp2.{$userfield}
                AND pp.fileid > pp2.fileid
            WHERE pp.projectid = '{$this->ProjectId()}'
                AND pp.fileid = '$pagename'";
        return $dpdb->SqlOneValue($sql);
    }

    public function ProoferRoundPageNameAfter($pagename, $roundid) {
        global $dpdb;
        $userfield = UserFieldForRoundId($roundid);
        $sql = "
            SELECT MIN(pp2.fileid) 
            FROM pages AS pp
            JOIN pages AS pp2 
                ON pp.projectid = pp2.projectid
                AND pp.{$userfield} = pp2.{$userfield}
                AND pp.fileid < pp2.fileid
            WHERE pp.projectid = '{$this->ProjectId()}'
                AND pp.fileid = '$pagename'";
        return $dpdb->SqlOneValue($sql);
    }

    public function LogProjectEvent($event_type, $remark = "") {
        global $dpdb;
        global $User;

        if($remark == "") {
        $n = $dpdb->SqlExecute("
            INSERT INTO project_events
            SET event_time = UNIX_TIMESTAMP(),
                projectid   = '{$this->Projectid()}',
                who         = '{$User->Username()}',
                task_code   = '{$this->RoundId()}',
                event_type  = '$event_type'");
        }
        else {
        $n = $dpdb->SqlExecute("
            INSERT INTO project_events
            SET event_time = UNIX_TIMESTAMP(),
                projectid   = '{$this->Projectid()}',
                who         = '{$User->Username()}',
                task_code   = '{$this->RoundId()}',
                event_type  = '$event_type',
                details     = '{$remark}'");
        }
        assert($n > 0);
    }

    private function WriteWordsArray($code, $langcode, $warray) {
        assert(is_array($warray));
        $warray = array_unique($warray);
        $warray = array_diff($warray, array(""));
        _write_words_array(
            $this->ProjectId(), $code, $langcode, $warray);
    }

    public function WriteGoodWordsList($langcode, $words) {
        $wary = list_to_unique_array($words);
        $this->WriteGoodWordsArray($langcode, $wary);
    }

    public function GoodWordCountHash($langcode) {
        $ary = $this->GoodWordsArray($langcode);
        return $this->WordCountHash($ary);
    }

//    public function GoodWordsList($langcode = null) {
//        if(! $langcode)
//            $langcode = $this->LanguageCode();
//        return $this->WordsList("good", $langcode);
//    }

//    public function BadWordsList($langcode = null) {
//        if(! $langcode)
//            $langcode = $this->LanguageCode();
//        return $this->WordsList("bad", $langcode);
//    }

    // apply words to current bad words list
    public function SubmitBadWordsArray( $langcode, $w_array) {
        $bwa = $this->BadWordsArray($langcode);
        foreach($w_array as $w) {
            $bwa[] = $w;
        }
        $bwa = array_unique($bwa);
        $this->WriteBadWordsArray($langcode, $bwa);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function BadWordLocationCount($langcode) {
        return count($this->BadWordCountHash($langcode));
    }

    public function WriteBadWordsList($langcode, $words) {
        $wary = list_to_unique_array($words);
        $this->WriteBadWordsArray($langcode, $wary);
    }

    // no counts, unique list
    public function GoodWordsArray($langcode) {
        return good_words_array($this->ProjectId(), $langcode);
    }

    public function GoodWordCountArray($langcode) {
        return $this->WordCountArray(
                            $this->GoodWordsArray($langcode));
    }

    public function WriteGoodWordsArray($langcode, $w_array) {
        $this->WriteWordsArray("good", $langcode, $w_array);
    }

    // apply words to current good words list
    // used to move suggested words to good list
    public function SubmitGoodWordsArray( $langcode, $w_array) {
        $gwa = $this->GoodWordsArray($langcode);
        foreach($w_array as $w) {
            $gwa[] = $w;
        }
        $gwa = array_unique($gwa);
        $this->WriteGoodWordsArray($langcode, $gwa);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function AddGoodWord($langcode, $word) {
        $this->SubmitGoodWordsArray($langcode, array($word));
    }

    public function AddBadWord($langcode, $word) {
        $this->SubmitBadWordsArray($langcode, array($word));
    }

    public function DeleteGoodWord($langcode, $word) {
        $ary = $this->GoodWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if($a == $word)
                continue;
            $out[] = $a;
        }
        $this->WriteGoodWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function DeleteBadWord($langcode, $word) {
        $ary = $this->BadWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if($a == $word)
                continue;
            $out[] = $a;
        }
        $this->WriteBadWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function DeleteSuggestedWordsArray($langcode, $awords) {
        $ary = $this->SuggestedWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if(in_array($a, $awords))
                continue;
            $out[] = $a;
        }
        $this->WriteSuggestedWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function DeleteGoodWordsArray($langcode, $awords) {
        $ary = $this->GoodWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if(in_array($a, $awords))
                continue;
            $out[] = $a;
        }
        $this->WriteGoodWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function DeleteBadWordsArray($langcode, $awords) {
        $ary = $this->BadWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if(in_array($a, $awords))
                continue;
            $out[] = $a;
        }
        $this->WriteBadWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    public function DeleteSuggestedWord($langcode, $word) {
        $ary = $this->SuggestedWordsArray($langcode);
        $out = array();
        foreach($ary as $a) {
            if($a == $word)
                continue;
            $out[] = $a;
        }
        $this->WriteSuggestedWordsArray($langcode, $out);
        $this->RefreshSuggestedWordsArray($langcode);
    }

    // simple array of spell words in no particular order.
    public function SpellWordsArray($langcode) {
        /** @var $ew DpEnchantedWords */
        $ew = $this->EnchantedWords($langcode);
        return array_unique($ew->WordsArray());
    }

    public function AdHocWordCountArray($langcode, $strwords) {
        $ary = text_to_words($strwords);
        return $this->WordCountArray($ary);
    }

    public function WordFindArray($strfind, $isregex, $isic) {
        if($strfind == "") {
            return array();
        }
        $flags = $isic ? "ui" : "u";
        $n = RegexCount($strfind, $flags, $this->Text());
        if($n > 1000) {
            die("More than 1000 matches found.");
        }

        return array($n, $strfind);
    }

    public function FlagWordsArray($langcode) {
        $s = $this->SpellWordsArray($langcode);
        $b =  $this->BadWordsArray($langcode);
        $g =  $this->GoodWordsArray($langcode);
        // $ary = $s + $b;
        $out = array();

        foreach($s as $a)
            if(! in_array($a, $g))
                $out[] = $a;

        foreach($b as $a)
            if(! in_array($a, $out))
                $out[] = $a;

        return $out;
    }

    public function FlagWordCountHash($langcode) {
        return $this->WordCountHash(
                            $this->FlagWordsArray($langcode));
    }

    public function FlagWordCountArray($langcode) {
        return $this->WordCountArray(
                            $this->FlagWordsArray($langcode));
    }

    public function FlagWordsByCountAlpha($langcode) {
        $sw = $this->FlagWordCountArray($langcode);
        sort_words_by_count_alpha($sw);
        return $sw;
    }

    public function ProjectWordCountsAlpha() {
        $sw = $this->ActiveWordCounts();
        $keys = array_keys($sw);
        natsort($keys);
        $ary = array();
        foreach($keys as $k) {
            $ary[$k] = $sw[$k];
        }
        return $ary;
    }

    public function ProjectWordsByCountAlpha() {
        $sw = $this->ActiveWordCounts();
        sort_words_by_count_alpha($sw);
        return $sw;
    }

    public function BadWordsArray($langcode) {
        return bad_words_array($this->ProjectId(), $langcode);
    }

    public function WriteBadWordsArray($langcode, $words_array) {
        $this->WriteWordsArray("bad", $langcode, $words_array);
    }

    public function GoodWordsByCountAlpha($langcode) {
        $wds = $this->GoodWordCountArray($langcode);
        sort_words_by_count_alpha($wds);
        return $wds;
    }

    public function BadWordsByCountAlpha($langcode) {
        $wds = $this->BadWordCountArray($langcode);
        sort_words_by_count_alpha($wds);
        return $wds;
    }

//    public function SpellWordsByCountAlpha($langcode) {
//        /** @var $sw DpEnchantedWords */
//        $sw = $this->EnchantedWords($langcode);
//        $wds = $sw->WordCounts();
//        sort_words_by_count_alpha($wds);
//        return $wds;
//    }

    public function SuggestedWordsByCountAlpha($langcode) {
        $a = $this->SuggestedWordCountArray($langcode);
        sort_words_by_count_alpha($a);
        return $a;
    }

    public function SpellWordCount($langcode) {
        $a1 = $this->EnchantedWords($langcode);
        $a2 = $this->GoodWordsArray($langcode);
        $total = 0;
        foreach($a1 as $w => $count) {
            if(! isset($a2[$w])) {
                $total += $count;
            }
        }
        return $total;
    }

    public function SuggestedWordCountHash($langcode) {
        return $this->WordCountHash(
                            $this->SuggestedWordsArray($langcode));
    }

    public function SuggestedWordCountArray($langcode) {
        return $this->WordCountArray(
                            $this->SuggestedWordsArray($langcode));
    }

    public function SuggestedWordsArray($langcode) {
        return suggested_words_array($this->ProjectId(), $langcode);
    }

    public function SuggestWordsArray($langcode, $w_array) {
        $this->SubmitSuggestedWordsArray($langcode, $w_array);
    }

    public function RefreshSuggestedWordsArray($langcode) {
        $s = $this->SuggestedWordsArray($langcode);
        $g = $this->GoodWordsArray($langcode);
        $b = $this->BadWordsArray($langcode);

        $map = $out = array();
        foreach($s as $word)
            $map[$word] = 1;

        foreach($g as $word)
            if(isset($map[$word]))
                $map[$word] = 0;
        
        foreach($b as $word)
            if(isset($map[$word]))
                $map[$word] = 0;
       
        foreach($map as $word => $ok)
            if($ok)
                $out[] = $word;

        $this->WriteSuggestedWordsArray( $langcode, $out);
    }

//    public function SuggestedWordsList($langcode) {
//        return $this->WordsList("suggested", $langcode);
//    }

    public function WriteSuggestedWordsList($langcode, $words) {
        $wary = list_to_unique_array($words);
        $this->WriteSuggestedWordsArray($langcode, $wary);
    }

    public function WriteSuggestedWordsArray($langcode, $words) {
        $this->WriteWordsArray("suggested", $langcode, $words);
    }

    // words accepted by wordcheck
    public function SubmitSuggestedWordsList($langcode, $words) {
        $wary = list_to_unique_array($words);
        $this->SubmitSuggestedWordsArray($langcode, $wary);
    }

    public function SubmitSuggestedWordsArray( $langcode, $w_array) {
        if(! is_array($w_array)) {
            return;
        }
        $s = $this->SuggestedWordsArray($langcode);
        foreach($w_array as $w) {
            $s[] = $w;
        }
        $s = array_unique($s);
        $this->WriteSuggestedWordsArray($langcode, $s);
        $this->RefreshSuggestedWordsArray($langcode);
    }


    private function WdiffFilePath() {
        return build_path($this->WordcheckPath(), WDIFF_FILENAME);
    }

    public function OCRTextFilePath() {
        return build_path($this->WordcheckPath(), OCR_FILENAME);
    }

    public function ActiveTextFilePath() {
        return build_path($this->WordcheckPath(), ACTIVE_TEXT_FILENAME);
    }

    private function TextLines() {
       return text_lines($this->Text());
    }

/*
    public function ActiveWdiff()
    {
        $return_code = "";
        file_put_contents($this->OCRTextFilePath(), $this->OCRText());
        file_put_contents($this->ActiveTextFilePath(), $this->ActiveText());
        $cmd = "wdiff -3 "
              ."{$this->OCRTextFilePath()} "
              ."{$this->ActiveTextFilePath()}";
        $ret = shell_exec($cmd);
        return $ret;
    }
*/

    private function WordCheckPath() {
        return ProjectWordCheckPath($this->ProjectId());
    }

    public function OCRTextWords() {
        return text_to_words($this->OCRText());
    }
    public function ActiveTextWords() {
        static $_ary;
        if(! isset($_ary)) {
            // $_ary = text_to_words($this->ActiveText());
            $_ary = text_to_words($this->ActiveText());
        }
        return $_ary;
    }
    public function Words() {
        return $this->ActiveTextWords();
    }
    public function UniqueWords() {
        return array_unique($this->ActiveTextWords());
    }

    public function ActiveWordCounts() {
        $ary = array();
        foreach($this->ActiveTextWords() as $w) {
            $ary[$w] = isset($ary[$w]) ? $ary[$w] + 1 : 1;
        }
        return $ary;
    }

    public function AdHocWordCountHash($word_list) {
        assert(! is_array($word_list));
        $word_array = list_to_unique_array($word_list);
        $ary = $this->WordCountHash($word_array);
        return $ary;
    }

    public function WordCountHash($w_array) {
        $wds = &$this->ActiveTextWords();
        $a = array();
        foreach($w_array as $w) {
            $w = (string) trim($w);
            if(isset($wds[$w])) {
                $a[$w]++;
            }
            else {
                $a[$w] = 1;
            }
        }
        return $a;
    }

    public function WordCountArray($w_array) {
        if(! is_array($w_array)) {
            assert(false);
            dump($w_array);
        }
        $wds = &$this->ActiveWordCounts();
        $a = array();
        // for each queried word
        foreach($w_array as $w) {
            if(isset($wds[$w])) {
                $a[] = array($w, $wds[$w]);
            }
            else {
                $a[] = array($w, 0);
            }
        }
        return $a;
    }

    public function BadWordCountHash($langcode) {
        return 
            $this->WordCountHash($this->BadWordsArray($langcode));
    }

    public function BadWordCountArray($langcode) {
        return $this->WordCountArray(
                            $this->BadWordsArray($langcode));
    }

/*
    public function PagesLines() {
        static $pls;
        if(! isset($pls)) {
            $pls = new PagesLines($this);
        }
        return $pls;
    }
*/

    public function ContextForOffset($word, $offset) {
        // say("offset: $offset");
        $pg = $this->PageForByteOffset($offset);
        $pagename = $pg->PageName();
        // say($pagename);
        $pgtext = $pg->ActiveText();
        $pgoffset = $this->ByteOffsetForPageName($pagename);
        $pglines = text_lines($pgtext);
        $pgposn = $offset - $pgoffset;
        $ln = RegexCount("\n", "u", bleft($pgtext, $pgposn));

        $ary = array();
        $ary['offset'] = $offset;
        $ary['word'] = $word;
        $ary['imageurl'] = $pg->ImageUrl();
        $ary['pagename'] = $pagename;
        $ary['lineindex'] = $ln + 1;
        $ary['linecount'] = count($pglines)-1;
        $l1 = max(0, $ln - 2);
        $context = implode("\n", array_slice($pglines, $l1, 5));
        $context = ReplaceRegex("(?<!\p{L})".$word."(?!\p{L})", 
                "<span class='wcontext'>{$word}</span>", "u", $context);
        $ary['context'] = $context;
        return $ary;
    }

    public function WordContexts($word) {
        
        // get project positions for the word
        $wdoffsets   = RegexWordOffsets($word, $this->ActiveText());
        $rsp         = array();
        $rsp['word'] = $word;
        $rsp['contexts'] = array();

        foreach($wdoffsets as $oset) {
            $offset = $oset[1];
            $wctxt = $this->ContextForOffset($word, $offset);
            $rsp['contexts'][] = $wctxt;
        }
        return $rsp;
    }

    public function RegexContexts($str, $flags) {
        $offsets     = 
            RegexOffsets($str, $flags, $this->Text());
        $rsp         = array();
        $rsp['word'] = $str;
        $rsp['contexts'] = array();

        foreach($offsets as $oset) {
            $match = $oset[0];
            $offset = $oset[1];
            $wctxt = $this->ContextForRegexOffset($match, $offset);
            $rsp['contexts'][] = $wctxt;
        }
        return $rsp;
    }

    public function ContextForRegexOffset($str, $offset) {
        // say("offset: $offset");
        $pg = $this->PageForByteOffset($offset);
        $pagename = $pg->PageName();
        // say($pagename);
        $pgtext = $pg->ActiveText();
        $pgoffset = $this->ByteOffsetForPageName($pagename);
        $pglines = text_lines($pgtext);
        $pgposn = $offset - $pgoffset;
        $ln = RegexCount("\n", "u", bleft($pgtext, $pgposn));

        $ary = array();
        $ary['offset'] = $offset;
        $ary['word'] = $str;
        $ary['imageurl'] = $pg->ImageUrl();
        $ary['pagename'] = $pagename;
        $ary['lineindex'] = $ln + 1;
        $ary['linecount'] = count($pglines)-1;
        $l1 = max(0, $ln - 2);
        $context = implode("\n", array_slice($pglines, $l1, 5));
        $context = ReplaceRegex("$str", 
                "<span class='wcontext'>{$str}</span>", "u", $context);
        $ary['context'] = $context;
        return $ary;
    }

//    public function WordContextInfo($word) {
//        $ary = array();
//        $wpa = $this->WordPageLineArray($word);
//    }

//    public function ProjectWordBytePositionInfo($word) {
//        $ary = array();
//        $wpa = $this->ProjectWordBytePositionArray($word);
//        if(count($wpa) == 0)
//            return $ary;
//        foreach($wpa as $wp) {
//            list($word, $posn) = $wp;
//            $pg       = $this->PageForByteOffset($posn);
//            $pagename = $pg->PageName();
//            $pgoffset = $this->PageNameByteOffset($pagename);
//            $woffset = $posn - $pgoffset;
//            $ary[] = array(
//               "word"      => $word,
//               "woffset"   => $woffset,
//               "pagename"  => $pagename,
//               "lineindex" => $pg->LineIndexForByteOffset($woffset)+1,
//               "linecount" => $pg->LineCount(),
//               "imageurl"  => raw_url_for_image_server(
//                                    $this->ProjectId(), $pagename),
//               "context"   => $pg->ThreeLinesForByteOffset($woffset)
//            );
//        }
//
//        return $ary;
//    }

//    public function ProjectWordBytePositionArray($word) {
//        // NOTE: position in bytes, not characters
//        return word_position_array($word, $this->Text());
//    }

    public function PageCharOffsetArray() {
        $ary = array();
        foreach($this->_page_char_offset_array as $pg => $os)
        {
            $ary[] = array("page" => $pg, "offset" => $os);
        }
        return $ary;
    }

    public function PageByteOffsetArray() {
        $ary = array();
        foreach($this->_page_byte_offset_array as $pg => $os)
        {
            $ary[] = array("page" => $pg, "offset" => $os);
        }
        return $ary;
    }

    public function PageNameByteOffset($pagename) {
        return $this->_page_byte_offset_array[$pagename];
    }

    public function PageNameByteOffsetEnd($pagename) {
        $a = &$this->_page_byte_offset_array;
        $ilim = count($a);
        for($i = 0; $i < $ilim; $i++) {
            if($a[$i]["page"] == $pagename) {
                if($ilim - $i > 1)
                    return $a[$i+1][1] - 1;
            }
        }
        return 0;
    }

    public function PageNameCharOffset($pagename) {
        return $this->_page_char_offset_array[$pagename];
    }

    public function ByteOffsetForPageName($pagename) {
        return $this->_page_byte_offset_array[$pagename];
    }

    public function CharOffsetForPagePosition($pagename, $position) {
        return $position + $this->_page_char_offset_array[$pagename];
    }

    public function PageNameForByteOffset($offset) {
        $ret = null;
        $a = $this->PageByteOffsetArray();
        if(count($a) == 0) {
            assert(false);
            return "";
        }

        foreach($a as $pg) {
            $pgoffset = $pg["offset"];
            if($offset < $pgoffset) {
                return $pg["page"];
            }
        }
        return null;
    }

    public function PageForByteOffset($offset) {
        $pn = $this->PageNameForByteOffset($offset);
        return ($pn == "")
            ? null
            : new DpPage($this->ProjectId(), $pn);
    }
}

function sort_words_by_count_alpha(&$word_array) {
    uasort($word_array,
        function ($w1, $w2) {
            if($w1[1] != $w2[1]) {
                return $w2[1] - $w1[1];
            }
            else {
                return $w1[0] == $w2[0]
                    ? 0
                    : $w1[0] > $w2[0]
                        ? 1
                        : -1
                      ;
            }
        }
    );
}

class DpProtoProject extends DpProject
{
    public function __construct() {
        global $Context;
        global $User;
        global $dpdb;

        $this->_projectid = $Context->NewProjectId();
        $sql = "
            INSERT INTO projects
            (
                 projectid,
                 nameofwork,
                 authorsname,
                 language,

                 username,
                 state,
                 comments,
                 difficulty,

                 clearance,

                 genre,
                 image_source
             )
             VALUES
             (
                '{$this->_projectid}',
                '**Title**',
                '**Author**',
                '{$User->Language()}',
                '{$User->Username()}',
                'OCR.".PROJ_WAITING_FOR_RELEASE."',
                '"._("**Project Comments**")."',
                'avg',
                '"._("**Project clearance**")."',
                'other',
                '"._("**Source of page images**")."'
            )";

        $dpdb->SqlExecute($sql);
        $this->LogProjectEvent(PJ_EVT_CREATE, "{$this->_projectid}");
        $this->query_for_row();
    }
}


?>
